package com.kool.leitu.base.excel;

import com.kool.leitu.base.exception.AppException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @description:
 * @author: tianhe
 * @create: 2019/02/01 17:20
 */
@Component
@Slf4j
public class ExcelReader {
    private static final String EXCEL_SUFFIX_XLS = "xls";
    private static final String EXCEL_SUFFIX_XLSX = "xlsx";


    public List<List<List<String>>> read(String filename, InputStream inputStream, int sheetBeg, int sheetEnd) {
        Workbook book = null;
        List<List<List<String>>> listSheet;
        try {
            if (filename.endsWith(EXCEL_SUFFIX_XLS)) {
                book = new HSSFWorkbook(inputStream);
            } else if (filename.endsWith(EXCEL_SUFFIX_XLSX)) {
                book = new XSSFWorkbook(inputStream);
            }
            listSheet = new ArrayList<>();
            if (sheetEnd > book.getNumberOfSheets()) {
                sheetEnd = book.getNumberOfSheets();
            }
            for (int i = sheetBeg; i < sheetEnd; i++) {
                List<List<String>> sheet = getSheetValue(book, i, 0, 65535, 0, 65535);
                listSheet.add(sheet);
            }
            book.close();
        } catch (FileNotFoundException e) {
            throw new AppException(1000001, e, "获取excel错误");
        } catch (IOException e) {
            throw new AppException(1000001, e, "获取excel错误");
        }
        return listSheet;
    }

    /**
     * @param book
     * @param sheetIndex
     * @param rowBegin
     * @param rowEnd
     * @param cellBegin
     * @param cellEnd
     * @return
     * @throws IOException
     * @DESCRIBE 获取excel的一页数据
     * @DATE 2017年8月25日 下午9:07:57
     */
    private List<List<String>> getSheetValue(Workbook book, int sheetIndex, int rowBegin, int rowEnd, int cellBegin,
                                             int cellEnd) {
        Sheet sheet = book.getSheetAt(sheetIndex);
        List<List<String>> listRow = new ArrayList<List<String>>();
        if (rowEnd > sheet.getLastRowNum()) {
            rowEnd = sheet.getLastRowNum();
        }
        for (int i = rowBegin; i <= rowEnd; i++) {
            Row row = sheet.getRow(i);
            List<String> listCell = new ArrayList<String>();
            if (cellEnd > row.getLastCellNum()) {
                cellEnd = row.getLastCellNum();
            }
            for (int j = 0; j < cellEnd; j++) {
                Cell cell = row.getCell(j);
                String cellValue = getCellValue(cell);
                listCell.add(cellValue);
            }
            listRow.add(listCell);
        }

        return listRow;
    }

    /**
     * @param cell
     * @return
     * @DESCRIBE 转单元格数据的格式，暂时只需要String类型
     * @DATE 2017-08-23 21:35:42
     */
    private String getCellValue(Cell cell) {
        cell.setCellType(CellType.STRING);
        return String.valueOf(cell.getStringCellValue());
    }
}
